
import com.mongodb.BasicDBObject;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.model.UpdateOptions;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.Document;

import java.io.File;
import java.io.FileInputStream;

public class ReadExcel {
    public static void main(String[] args) {
        MongoClient mongoClient = new MongoClient("192.168.1.171", 30000);
        MongoCollection<Document> target = mongoClient.getDatabase("aitexts_宏观策略").getCollection("RawData");
        //读取表格中的数据
        File[] f_all = new File("F:\\研究报告\\宏观策略").listFiles();
        for (File f:f_all) {
            if(f.isFile() && f.getName().endsWith(".xlsx")){
                Excel2MongoDb(f,target);
            }
        }
    }

    static void Excel2MongoDb(File f, MongoCollection<Document> target){
        System.out.println(f.getName());
        Workbook workbook;
        try {
            workbook = new XSSFWorkbook(new FileInputStream(f.getAbsolutePath()));
            Sheet sheetAt = workbook.getSheetAt(0);
            for (int i = 1; i <= sheetAt.getLastRowNum(); i++) {
                System.out.println("已获取一条信息======================" + i);
                String Date = (sheetAt.getRow(i).getCell(1) == null) ? null : sheetAt.getRow(i).getCell(1).toString();
                String Industry = (sheetAt.getRow(i).getCell(2) == null) ? null : sheetAt.getRow(i).getCell(2).toString();
                String title = (sheetAt.getRow(i).getCell(3) == null) ? null : sheetAt.getRow(i).getCell(3).toString();
                String B_name = (sheetAt.getRow(i).getCell(4) == null) ? null : sheetAt.getRow(i).getCell(4).toString();
                String Author = (sheetAt.getRow(i).getCell(5) == null) ? null : sheetAt.getRow(i).getCell(5).toString();

//                String Rate = (sheetAt.getRow(i).getCell(6) == null) ? null : sheetAt.getRow(i).getCell(6).toString();
//                String Change = (sheetAt.getRow(i).getCell(7) == null) ? null : sheetAt.getRow(i).getCell(7).toString();
                String Pages = (sheetAt.getRow(i).getCell(6) == null) ? null : sheetAt.getRow(i).getCell(6).toString();
                String id = (sheetAt.getRow(i).getCell(8) == null) ? null : sheetAt.getRow(i).getCell(8).toString();
                Document document = new Document("_id",id);
                document.append("B_name",B_name);
                document.append("Date",Date);
                document.append("Industry",Industry);
                document.append("Author",Author);
//                document.append("Rate",Rate);
//                document.append("Change",Change);
                document.append("Pages",Pages);
                document.append("title",title);
//                System.out.println(id);
                target.updateOne(new BasicDBObject("_id", document.getString("_id")), new BasicDBObject("$set", document), (new UpdateOptions()).upsert(true));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
